﻿/***********************************************************************************************************************************
 *    Name: XE_Import.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Read the Extended Events file into a SQL table for analysis.
CREATE TABLE dbo.event_file (
	instance_name	SYSNAME				NOT Null,
	timestamp		DATETIME2(3)			Null,
	package_name	NVARCHAR(128)			Null,
	event_name		NVARCHAR(128)		NOT Null,
	event_data		XML					NOT NULL);
CREATE CLUSTERED INDEX CX_event_file ON dbo.event_file (instance_name, timestamp, package_name, event_name);
**/
GO

WITH e (event_name, event_data) AS (SELECT object_name, CAST(event_data AS XML) FROM sys.fn_xe_file_target_read_file(N'*.xel', Null, Null, Null))
INSERT INTO dbo.event_file (instance_name, timestamp, package_name, event_name, event_data)
SELECT
	instance_name	= N'(LOCAL)',
	timestamp		= event_data.value ('(/event/@timestamp)[1]', 'DATETIME2(3)' ),
	package_name	= event_data.value ('(/event/@package  )[1]', 'NVARCHAR(128)'),
	event_name,
	event_data
  FROM e;
GO